#coding:utf-8
import sys
import pandas as pd
import matplotlib.pyplot as plt
import MySQLdb
reload(sys)
#修改控制台的编码格式
sys.setdefaultencoding('utf-8')

#链接数据库
conn = MySQLdb.connect(host="127.0.0.1", user='root', passwd='root', db='ca', port=3306,
                       charset='utf8')
cursor = conn.cursor()
#检测平均月薪和工作地点的关系
sql="select avg(zwyx) AS avg_zwyx,dd_id from ca_list GROUP BY dd_id ORDER BY dd_id"#公司地点
sql2="select avg(zwyx) AS avg_zwyx,zwmc_id from ca_list GROUP BY zwmc_id HAVING avg_zwyx!=0 ORDER BY zwmc_id limit 1,100"#职位名称
sql3="select avg(zwyx) AS avg_zwyx,gsmc_id from ca_list GROUP BY gsmc_id ORDER BY gsmc_id limit 1,100"#公司名称
sql4="select avg(zwyx) AS avg_zwyx,gsxz_id from ca_list GROUP BY gsxz_id ORDER BY gsxz_id"#公司性质
sql5="select avg(zwyx) AS avg_zwyx,gsgm_id from ca_list GROUP BY gsgm_id ORDER BY gsgm_id"#公司规模
sql7="select avg(zwyx) AS avg_zwyx,xl_id from ca_list GROUP BY xl_id ORDER BY xl_id"#学历
sql8="select avg(zwyx) AS avg_zwyx,jy_id from ca_list GROUP BY jy_id ORDER BY jy_id"#工作经验
#执行生成dataFrame数据
df = pd.read_sql(sql,conn)
loandata=pd.DataFrame(df)
#执行生成dataFrame数据
df2 = pd.read_sql(sql2,conn)
loandata2=pd.DataFrame(df2)
#执行生成dataFrame数据
df3 = pd.read_sql(sql3,conn)
loandata3=pd.DataFrame(df3)
#执行生成dataFrame数据
df4 = pd.read_sql(sql4,conn)
loandata4=pd.DataFrame(df4)
#执行生成dataFrame数据
df5 = pd.read_sql(sql5,conn)
loandata5=pd.DataFrame(df5)
#执行生成dataFrame数据
df7 = pd.read_sql(sql7,conn)
loandata7=pd.DataFrame(df7)
#执行生成dataFrame数据
df8 = pd.read_sql(sql8,conn)
loandata8=pd.DataFrame(df8)
# print df


#2.数据表中空值处理
#将包含空值的数据删除
# print loandata2.fillna(0)
# loandata2
#创建一个新的Figure
plt.figure(1)
plt.subplot(711)
plt.plot(loandata.ix[:,'dd_id'],loandata.ix[:,'avg_zwyx'])
plt.subplot(712)
plt.bar(loandata2.ix[:,'zwmc_id'],loandata2.ix[:,'avg_zwyx'])
plt.subplot(713)
plt.bar(loandata3.ix[:,'gsmc_id'],loandata3.ix[:,'avg_zwyx'])
plt.subplot(714)
plt.plot(loandata4.ix[:,'gsxz_id'],loandata4.ix[:,'avg_zwyx'])
plt.subplot(715)
plt.plot(loandata5.ix[:,'gsgm_id'],loandata5.ix[:,'avg_zwyx'])
plt.subplot(716)
plt.plot(loandata7.ix[:,'xl_id'],loandata7.ix[:,'avg_zwyx'])
plt.subplot(717)
plt.plot(loandata8.ix[:,'jy_id'],loandata8.ix[:,'avg_zwyx'])
plt.show()



# plt.xlabel(u"city")
# # plt.xticks(loandata.ix[:,'gzdd'],loandata2.ix[:,'name'])
# plt.ylabel(u"money")
# plt.subplot(212)
# plt.bar(loandata.ix[:,'zwmc'],loandata.ix[:,'avg_zwyx'])
# # plt.bar()
# # plt.legend()
# figp=plt.figure()
# a1=figp.add_subplot(2,1,1)
# a2=figp.add_subplot(2,1,2)
# a3=figp.add_subplot(2,1,3)
# a1.plot(loandata.ix[:,'gzdd'],loandata.ix[:,'avg_zwyx'],label=u"城市和薪资关系")
# a2.plot(loandata.ix[:,'zwmc'],loandata.ix[:,'avg_zwyx'],label=u"城市和薪资关系")
# a3.plot(loandata.ix[:,'gsgm'],loandata.ix[:,'avg_zwyx'],label=u"城市和薪资关系")
# a1.ylabel(u"薪资")
# a2.ylabel(u"薪资")
# a3.ylabel(u"薪资")
# # a1.grid(True)
# # a1.legend()
# a1.show()
# # a1.grid(True)
# # a1.legend()
# a2.show()
# a3.show()
#
# #1.数据表中的重复值
# #对数据表进行重复值查找后的结果
# loandata.duplicated()
# #删除数据表中的重复值
# loandata.drop_duplicates()
#
# #2.数据表中空值处理
# #将包含空值的数据删除
# loandata.dropna()
#
#确定使用的图表
# mpl.use('Agg')
#
# # 字体大小
# font_size = 10
# # 图表大小
# fig_size = (8, 6)
#
# # 更改默认更新字体大小
# mpl.rcParams['font.size'] = font_size
# # 修改默认更新图表大小
# mpl.rcParams['figure.figsize'] = fig_size


